package com.topisv.tms.utils.excel;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.topisv.tms.entity.CustomerLead;
import com.topisv.tms.utils.DateUtils;
import com.topisv.tms.utils.lead.ExcelUtil;
import com.topisv.tms.utils.lead.LeadUtil;

/**
 * excel导入到系统的工具类
 * @author Administrator
 *
 */
@Component
public class ExcelImportUtil {
	protected static Logger logger = LoggerFactory.getLogger(LeadUtil.class);
	
	/**
	 * excel导入到实体对应的表中
	 * @param items
	 * @param entity
	 * @throws IOException
	 */
	@Deprecated
	public <T> void excelToEntity(List<FileItem> items,T entity) throws IOException {
		InputStream stream = null;
		Workbook rwb = null;
		
		if(null==items){
			return;
		}
		
		Iterator<FileItem> itr = items.iterator();
		while (itr.hasNext()) {//遍历文件
			FileItem item = (FileItem) itr.next();
			if (!item.isFormField()) {//是文件上传类型
				stream = item.getInputStream();
				if (item.getName().endsWith(".xls")) {// 解析xls
					rwb = new HSSFWorkbook(stream);
					int count = rwb.getNumberOfSheets();
					//遍历文件的工作表
					for (int i = 0; i < count; i++) { 
						Sheet sheet = rwb.getSheetAt(i);
						int rows=sheet.getLastRowNum();
						if(rows>0){
						}
					}
				} else if (item.getName().endsWith(".xlsx")) {// 解析xlsx
					rwb = new XSSFWorkbook(stream);
					int count = rwb.getNumberOfSheets();
					for (int i = 0; i < count; i++) {
						XSSFSheet sheet = (XSSFSheet) rwb.getSheetAt(i);
						int rows=sheet.getLastRowNum();
						if(rows>0){
						}
					}
				}

			}
		}
		
	}
	
	@SuppressWarnings("unused")
    @Deprecated
	public static <T> List<T> getEdiGoodsSkus(Sheet s) {
		List<T> tList=new ArrayList<T>();
		
		int rows = s.getLastRowNum();
		// 遍历excel行
		for (int i = 0; i <= rows; i++) {
			Row xSSFRow = s.getRow(i);
			int columns = xSSFRow.getLastCellNum();
			// 遍历excel列
			for (int j = 0; j < columns; j++) {
				Cell cell = xSSFRow.getCell(j);
				if (cell == null) {
					continue;
				}
				String content = ExcelUtil.getCellStringValue(cell);
				if (i == 0) {
					continue;
				}

				try {
					// replaceParams(title, content, e);
				} catch (Exception e2) {
					logger.error("some error.", e2);
				}

			}
		}
		return tList;
	}
	
	/**
	 * 将excel文件中的数据，转化成指定的对象集合
	 * @param customerLead 模板对象
	 * @param s excel对象
	 * @param clazz 需要转化的对象
	 * @param needModel 是否需要模板
	 * @return 对象集合
	 * @throws 
	 * @author <a href=mailto:libin@nryuncang.com>libin</a>
	 * @version 1.0.0
	 */
	public static <T> List<T> getObjectOfExcel(CustomerLead customerLead, Sheet s,Class<T> clazz,Boolean needModel) throws Exception {
        List<T> tObject = null;
        try {
            tObject = new ArrayList<T>();
            List<String> sequence = new ArrayList<String>();
            if (customerLead != null && needModel == true) { //需要模板，而且存在模板
                Map<String, String> maps = new HashMap<String, String>();
                for (String key : customerLead.getParams().split(",")) {
                    maps.put(key.split(":")[0], key.split(":")[1]);
                }
                for (int i = 0; i <= s.getLastRowNum(); i++) {
                    Row xSSFRow = s.getRow(i);
                    T e = clazz.newInstance();
                    for (int j = 0; j < xSSFRow.getLastCellNum(); j++) {
                        Cell cell = xSSFRow.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        String content = ExcelUtil.getCellStringValue(cell);
                        if (i == 0) {
                            String value = maps.get(content);
                            sequence.add(value);
                            continue;
                        }
                        if (sequence.get(j) == null) {
                            continue;
                        }
                        replaceParams(sequence.get(j), content, e);
                    }
                    tObject.add(e);
                }
            }else {   //不需要模板
                for (int i = 0; i <= s.getLastRowNum(); i++) {
                    Row xSSFRow = s.getRow(i);
                    T e = clazz.newInstance();
                    for (int j = 0; j < xSSFRow.getLastCellNum(); j++) {
                        Cell cell = xSSFRow.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        String content = ExcelUtil.getCellStringValue(cell);
                        if (i == 0) {
                            String value = getExcelTitle().get(content);
                            sequence.add(value);
                            continue;
                        }
                        if (sequence.get(j) == null) {
                            continue;
                        }
                        replaceParams(sequence.get(j), content.trim(), e);
                    }
                   if(i>0) tObject.add(e);
                }
            }
        } catch (Exception e) {
            logger.error("getObjectOfExcel error.",e);
        }
        return tObject;
    }
    
	/**
	 * Excel首列和数据库的默认匹配
	 * @param 
	 * @return 
	 * @throws 
	 * @author <a href=mailto:libin@nryuncang.com>libin</a>
	 * @version 1.0.0
	 */
    private static Map<String, String> getExcelTitle() {
        Map<String, String> titleMap= new HashMap<String, String>();
        titleMap.put("订单号", "orderNo");
        titleMap.put("运单号", "expressNo");
        return titleMap;
    }
    
    /**
    * 利用反射给实体赋值
    * @param param 字段
    * @param content 字段对应的值
    * @param e 操作的实体
    * @return 
    * @throws 
    * @author <a href=mailto:libin@nryuncang.com>libin</a>
    * @version 1.0.0
     * @param <T>
    */
    private static  <T> void replaceParams(String param, String content, T e) throws Exception {
        Field[] fields = e.getClass().getDeclaredFields();
        for (int j = 0; j < fields.length; j++) { // 遍历所有属性
            String name = fields[j].getName(); // 获取属性的名字
            if (!param.equals(name)) {
                continue;
            }
            name = name.substring(0, 1).toUpperCase() + name.substring(1); // 将属性的首字符大写，方便构造get，set方法
            String type = fields[j].getGenericType().toString(); // 获取属性的类型
            if (type.equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class "，后面跟类名
                Method m = e.getClass().getMethod("set" + name, String.class);
                m.invoke(e, content);
            } else if (type.equals("class java.lang.Integer")) {
                int index = content.indexOf(".");
                Method m = e.getClass().getMethod("set" + name, Integer.class);
                if (index > 0) {
                    m.invoke(e, Integer.parseInt(content.substring(0, index)));
                } else {
                    m.invoke(e, Integer.parseInt(content));
                }

            } else if (type.equals("class java.lang.Double")) {
                Method m = e.getClass().getMethod("set" + name, Double.class);
                m.invoke(e, Double.parseDouble(content));
            }else if (type.equals("class java.util.Date")) {
                Method m = e.getClass().getMethod("set" + name, Date.class);
                m.invoke(e,DateUtils.getDate(content));
            }
            break;
        }
    }
}
